﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Yunlib.Common
{
    public class NpoiOffice97Export 
    {
        public void Export(DataTable exportTable, Dictionary<string, string> exportColumns, string exportPath)
        {
            using (MemoryStream ms = ExportDataTableToExcel(exportTable, exportColumns))
            {
                using (FileStream fs = new FileStream(exportPath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }

               
            }
        }

        public MemoryStream ExportDataTableToExcel(DataTable sourceTable, Dictionary<string, string> exportColumns)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            int dtRowsCount = sourceTable.Rows.Count;
            int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / 65536));
            int SheetNum = 1;
            int rowIndex = 1;
            int tempIndex = 1; //标示 
            List<string> OriginalColumns = new List<string>();
            ISheet sheet = workbook.CreateSheet("sheet1" + SheetNum);
            for (int i = 0; i < dtRowsCount; i++)
            {
                if (i == 0 || tempIndex == 1)
                {
                    IRow headerRow = sheet.CreateRow(0);
                    //重修而导出列头
                    int columnIndex = 0;
                    foreach (var key in exportColumns.Keys)
                    {
                        if (sourceTable.Columns.Contains(key))
                        {
                            headerRow.CreateCell(columnIndex++).SetCellValue(exportColumns[key]);

                            OriginalColumns.Add(key);
                        }
                    }
                }
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex);

                for (int j = 0, l = OriginalColumns.Count; j < l; j++)
                {
                    dataRow.CreateCell(j).SetCellValue(sourceTable.Rows[i][OriginalColumns[j]].ToString());
                }
                if (tempIndex == 65535)
                {
                    SheetNum++;
                    sheet = workbook.CreateSheet("sheet" + SheetNum);//
                    tempIndex = 0;
                }
                rowIndex++;
                tempIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            workbook = null;
            return ms;
        }

    }
}
